from osv import osv, fields
from osv.orm import intersect
import tools.sql

class kdvn_project(osv.osv):
    _name = "kdvn.project"
    _description="KDVN Projects"

    def _delete_all_budget_line(self,cr,uid,ids,context):
        kdvn_budget_data_ids=[]
        kdvn_budget_data_obj =self.read(cr,uid,ids,['budget_data_ids'])
        if kdvn_budget_data_obj:
            kdvn_budget_data_ids =kdvn_budget_data_obj[0]['budget_data_ids'] 
        if kdvn_budget_data_ids:
            self.pool.get('kdvn.budget.data').unlink(cr,uid,kdvn_budget_data_ids,context)        
        return True
    
    def unlink(self, cr, uid, ids, context=None):
        if ids:
            prj_ids = ",".join(map(str,ids))
            if self.pool.get('kdvn.budget.data').search(cr,uid,[('project_id','in',ids)]):
                raise osv.except_osv('Warning action !','Cannot delete Project(s), You must delete all Budget related!')
            if self.pool.get('project.currency').search(cr,uid,[('project_id','in',ids)]):
                raise osv.except_osv('Warning action !','Cannot delete Project(s), You must delete all Currency related!')
            if self.pool.get('sale.order').search(cr,uid,[('project_e_id','in',ids)]) or self.pool.get('sale.order').search(cr,uid,[('project_m_id','in',ids)]):
                raise osv.except_osv('Warning action !','Cannot delete Project(s), You must delete all Quotation related!')
        unlink_ids = ids
        return super(kdvn_project, self).unlink(cr, uid, unlink_ids, context=context)
        
    #def create(self, cr, uid, vals, context={}):        
     #   id_test=osv.osv.create(self, cr, uid, vals, context=context)
      #  new_id=self.pool.get('project.currency')._create_currency(cr,uid,id_test,'project',context)
       # return id_test
        
    def _get_project_currency(self,cr,uid,ids,name,args,context={}):
        res={}
        if ids:
            acc_set=",".join(map(str,ids))
            cr.execute("Select project_id,id from project_currency where default_curr=True and project_id in (%s)" % acc_set)
            for ctr_id,curr_id in cr.fetchall():
                res[ctr_id]=curr_id#,'contract_rate':curr_rate}
        for id in ids:
            res[id]=res.get(id,False)
        #raise osv.except_osv("E",res)
        return res

    def _update_history(self, cr, uid, ids,context):
        for kp in self.browse(cr,uid,ids):
            self.pool.get('kdvn.budget.history').create_history(cr,uid,kp.id)
        return True
    
    def _get_quotation_lists(self, cr, uid, ids, name, arg, context=None):
        res = {}
        for id in ids:
            cr.execute('SELECT id FROM sale_order ' \
                    'WHERE project_e_id =%s or project_m_id=%s' % (id,id))
            res[id] = [x[0] for x in cr.fetchall()]
        return res
    
    def _get_contract_lists(self, cr, uid, ids, name, arg, context=None):
        res={}
        for id in ids:
            cr.execute("Select contract_id from sale_order where state not in ('draft','cancel') and contract_id>0 and (project_e_id = %s or project_m_id = %s) group by contract_id" %(id,id))
            res[id] = [x[0] for x in cr.fetchall()]
        return res
    
    def _get_direct_cost_bak(self, cr, uid, ids, name, arg, context=None):
        res = {}
        if ids:
            lst_tmp={'direct_amount_vnd':0.0,
                     'direct_amount_usd':0.0}
            for id in ids:
                res[id]=lst_tmp
            prj_id_list = ",".join(map(str,ids))
            cr.execute("select \
                            kbd.project_id,\
                            sum(budget_amount) as direct_cost_vnd,\
                            sum(case \
                                                    when rate =0\
                                                    then 0 \
                                                    else coalesce(budget_amount/rate,0) end) as direct_cost_usd\
                        from \
                            kdvn_budget_data kbd \
                        left join\
                            project_currency pc on kbd.project_id = pc.project_id and pc.name='USD'\
                        left join\
                            account_budget_post abp on kbd.budget_id=abp.id\
                        left join\
                            budget_category bc on categ_id = bc.id\
                        left join\
                            budget_category bc1 on bc.parent_id = bc1.id\
                        where \
                            bc1.cat_code='direct_cost' and kbd.project_id in (%s)\
                        group by\
                            kbd.project_id" % prj_id_list)
            for prj_id,direct_vnd,direct_usd in cr.fetchall():
                res[prj_id]['direct_amount_vnd']=direct_vnd
                res[prj_id]['direct_amount_usd']=direct_usd
        return res
    
    def _get_direct_cost_usd(self, cr, uid, ids, name, arg, context=None):
        res = {}
        if ids:
            prj_id_list = ",".join(map(str,ids))
            for id in ids:
                res[id]=0.0
            cr.execute("select \
                            kp.id,\
                            sum(case \
                                when rate =0\
                                then 0 \
                                else coalesce(budget_amount/rate,0) end) as direct_cost_usd\
                        from\
                            kdvn_project kp\
                        left join\
                            kdvn_budget_data kbd on kp.id = kbd.project_id\
                        left join\
                            project_currency pc on kp.id = pc.project_id and pc.name='USD'\
                        left join\
                            account_budget_post abp on kbd.budget_id=abp.id\
                        left join\
                            budget_category bc on categ_id = bc.id\
                        left join\
                            budget_category bc1 on bc.parent_id = bc1.id\
                        where \
                            bc1.cat_code='direct_cost' and kp.id in (%s)\
                        group by\
                            kp.id" % prj_id_list)
            for prj_id,direct_usd in cr.fetchall():
                res[prj_id] = direct_usd
        return res
      
    def name_get(self, cr, uid, ids, context={}):
        if not len(ids):
            return []
        res = []
       
        d1, d2 = self.pool.get('ir.rule').domain_get(cr, uid, 'kdvn.project')
        #raise osv.except_osv("E",d1)
        if d2:
            tmp_ids = ids
            ids = filter(lambda x:x in d2,ids)
            ids_access_denied = filter(lambda x:x not in d2,tmp_ids)
            for id in ids_access_denied:
                res.append((id,'// Access Denied //'))
        reads = self.read(cr, uid, ids, ['name','code'], context)
        code_only = False
        if 'code_only' in context:
            code_only = context['code_only']
        
        for record in reads:
            name=''
            if record:
                if code_only:
                    name = record['code']
                else:
                    name = record['code']+" - "+record['name']
                res.append((record['id'], name))
        
        return res
    
    def _get_project_cost_info(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            project_info_lists={
                                'project_budget_total_vnd':0.0,'project_budget_total_usd':0.0,
                                'expense_vnd':0.0,'expense_usd':0.0,
                                'payment_vnd':0.0,'payment_usd':0.0}#'project_amount_vnd':0.0,'project_vat_vnd':0.0,'project_total_vnd':0.0,
#Claim and Received Amount in VND
                                #'contract_amount': 0.0,'contract_vat': 0.0,'contract_total': 0.0,
#                                'prj_claim_amount_vnd': 0.0,'prj_claim_amount_usd': 0.0,
#                                'prj_recv_amount_vnd': 0.0, 'prj_recv_amount_usd': 0.0,
                                
            for id in ids:
                res[id]=project_info_lists
            prj_id_list = ",".join(map(str, ids))
            
#            \
#            coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_amount*pc.rate/pc_vnd.rate end),0) as project_amount_vnd,\
#            coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_vat*pc.rate/pc_vnd.rate end),0) as project_vat_vnd,\
#            coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_total*pc.rate/pc_vnd.rate end),0) as project_total_vnd,\
            
#             \coalesce((case when coalesce(pc_usd.rate,0)=0 then 0 else project_amount*pc.rate/pc_usd.rate end),0) as project_amount_usd,\
#            coalesce((case when coalesce(pc_usd.rate,0)=0 then 0 else project_vat*pc.rate/pc_usd.rate end),0) as project_vat_usd,\
#            coalesce((case when coalesce(pc_usd.rate,0)=0 then 0 else project_total*pc.rate/pc_usd.rate end),0) as project_total_usd,\
            cr.execute("Select \
                            kp.id,\
                            \
                            coalesce(sum(budget_amount),0) as prj_budget_total\
                        from \
                            kdvn_project kp\
                        left join\
                            kdvn_budget_data kbd on kp.id = kbd.project_id\
                        where \
                            kp.id in (%s)\
                        group by kp.id" % (prj_id_list))
        for prj_list in cr.dictfetchall():
            tmp_id=prj_list.pop('id')
            res[tmp_id].update(prj_list)
        return res
    
    def _get_project_cost_info_bak(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            project_info_lists={'project_amount_vnd':0.0,'project_vat_vnd':0.0,'project_total_vnd':0.0,
                                'project_amount_usd':0.0,'project_vat_usd':0.0,'project_total_usd':0.0,
                                'project_budget_total_vnd':0.0,'project_budget_total_usd':0.0,
#Claim and Received Amount in VND
                                #'contract_amount': 0.0,'contract_vat': 0.0,'contract_total': 0.0,
#                                'prj_claim_amount_vnd': 0.0,'prj_claim_amount_usd': 0.0,
#                                'prj_recv_amount_vnd': 0.0, 'prj_recv_amount_usd': 0.0,
                                'expense_vnd':0.0,'expense_usd':0.0,
                                'payment_vnd':0.0,'payment_usd':0.0}
            for id in ids:
                res[id]=project_info_lists
            prj_id_list = ",".join(map(str, ids))
            
            cr.execute("Select \
                            kp.id,\
                            \
                            coalesce(sum(budget_amount),0) as project_budget_total_vnd,\
                            coalesce(sum(case when coalesce(pc_usd.rate,0)=0 then 0 else budget_amount/pc_usd.rate end),0) as project_budget_total_usd,\
                            \
                            coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_amount*pc.rate/pc_vnd.rate end),0) as project_amount_vnd,\
                            coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_vat*pc.rate/pc_vnd.rate end),0) as project_vat_vnd,\
                            coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_total*pc.rate/pc_vnd.rate end),0) as project_total_vnd,\
                            \
                            coalesce((case when coalesce(pc_usd.rate,0)=0 then 0 else project_amount*pc.rate/pc_usd.rate end),0) as project_amount_usd,\
                            coalesce((case when coalesce(pc_usd.rate,0)=0 then 0 else project_vat*pc.rate/pc_usd.rate end),0) as project_vat_usd,\
                            coalesce((case when coalesce(pc_usd.rate,0)=0 then 0 else project_total*pc.rate/pc_usd.rate end),0) as project_total_usd,\
                            \
                            (coalesce(subtotalvnd,0)) as expense_vnd,\
                            (coalesce(subtotalusd,0)) as expense_usd,\
                            (coalesce(payment_vnd,0)) as payment_vnd,\
                            (coalesce(payment_usd,0)) as payment_usd \
                        from \
                            kdvn_project kp\
                        left join\
                            project_currency pc on kp.id = pc.project_id and default_curr=True\
                        left join\
                            project_currency pc_usd on kp.id=pc_usd.project_id and pc_usd.name='USD'\
                        left join\
                            project_currency pc_vnd on  kp.id=pc_vnd.project_id and pc_vnd.name='VND'\
                        left join\
                            kdvn_budget_data kbd on kp.id = kbd.project_id\
                        left join\
                            (Select\
                                pol.project_id,\
                                coalesce(sum(price_subtotal2*rate*fnpo_compute(fnpo_currnametoid('VND'),po.currency_id,date_order)),0) as subtotalvnd,\
                                coalesce(sum(price_subtotal2*fnpo_compute(fnpo_currnametoid('USD'),po.currency_id,date_order)),0) as subtotalusd,\
                                sum(coalesce(kropd.amount,0)*coalesce(rate,1)*coalesce(fnpo_compute(fnpo_currnametoid('VND'),krop.currency_id,date_order),0)) as payment_vnd,\
                                sum(coalesce(kropd.amount,0)*coalesce(fnpo_compute(fnpo_currnametoid('USD'),krop.currency_id,date_order),0)) as payment_usd\
                            from \
                                purchase_order po\
                            left join \
                                purchase_order_line pol on po.id=order_id\
                            left join \
                                tbltmp_po_rate vwr on po.id=vwr.id\
                            left join\
                                kdvn_request_of_payment krop on po.id=krop.order_id and krop.state<>'draft' and krop.state<>'cancel'\
                            left join\
                                kdvn_request_of_payment_detail kropd on krop.id=kropd.rop_id\
                            left join \
                                (Select distinct\
                                    payment_id\
                                from\
                                    kdvn_payment) kp on krop.id=payment_id and kp.payment_id>0\
                            where\
                                po.state not in ('cancel','draft')\
                            group by\
                                pol.project_id) vwexpense on kp.id=vwexpense.project_id\
                        where \
                            kp.id in (%s)\
                        group by kp.id,\
                            pc_vnd.rate,project_amount,\
                            project_vat,project_total,\
                            pc_usd.rate,pc.rate,\
                            expense_vnd,expense_usd,\
                            payment_vnd,payment_usd" % (prj_id_list))
        for prj_list in cr.dictfetchall():
            tmp_id=prj_list.pop('id')
            res[tmp_id].update(prj_list)
        return res
    
    def _get_project_cost_claim_recv_info(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            project_info_cost_claim_lists={'prj_contracted_amount': 0.0,'prj_claimed_amount': 0.0}
            for id in ids:
                res[id]=project_info_cost_claim_lists
            prj_id_list = ",".join(map(str, ids))
            
            cr.execute("""Select
                            kp.id,kp.code,
                            sum(contracted_amount*(case when project_type='E' then coalesce(per_job_e,0) else coalesce(per_job_m,0) end)) as prj_contracted_amount,
                            sum(claimed_amount*(case when project_type='E' then coalesce(per_job_e,0) else coalesce(per_job_m,0) end)) as prj_claimed_amount
                        from
                            kdvn_project kp
                        left join
                            sale_order so on kp.id = project_e_id or kp.id = project_m_id
                        left join
                            (Select
                                quo_id,
                                case when 
                                    sum(coalesce(amount,0))=0 then 0 
                                else 
                                    sum(case when combine='E' then coalesce(amount,0) else 0 end)/sum(coalesce(amount,0)) 
                                end as per_job_e,
                                case when 
                                    sum(coalesce(amount,0))=0 then 0 
                                else 
                                    sum(case when combine='M' then coalesce(amount,0) else 0 end)/sum(coalesce(amount,0)) 
                                end as per_job_m
                            from
                                (Select
                                    so.id as quo_id,
                                    project_e_id as prj_id,
                                    sum(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'internal')*(sol.amount+sol.discount)) as amount,
                                    'E' as combine
                                from
                                    sale_order so
                                left join
                                    sale_order_line sol on so.id = sol.order_id and job_type='E'
                                where 
                                    coalesce(project_e_id,0)>0 and coalesce(contract_id,0)>0
                                group by
                                    contract_id,project_e_id,so.id
                                Union
                                Select
                                    so.id as quo_id,
                                    project_m_id as prj_id,
                                    sum(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'internal')*(sol.amount+sol.discount)) as amount,
                                    'M' as combine
                                from
                                    sale_order so
                                left join
                                    sale_order_line sol on so.id = sol.order_id and job_type='M'
                                where 
                                    coalesce(project_m_id,0)>0 and coalesce(contract_id,0)>0
                                group by
                                    contract_id,project_m_id,so.id) vwcombine_quos
                            group by
                                quo_id) vwjob_per on so.id = quo_id
                        left join
                            (Select
                                kcc.id,
                                coalesce(claimed_amount,0) as claimed_amount,
                                sum(coalesce(fncompute(kitc.id,get_default_currency(),registration_date,'international'),0)*(coalesce(camt,0)- coalesce(claim_amount,0))) + coalesce(claimed_amount,0) as contracted_amount
                            from
                                kdvn_contract_client kcc
                            left join
                                (Select
                                    contract_id,
                                    sum(amount) as camt,
                                    internal_curr_id as currency_id
                                from
                                    (Select 
                                    so.contract_id,
                                    sol.currency_id as internal_curr_id,
                                    coalesce(sol.amount,0)+coalesce(sol.discount,0) as amount
                                    from 
                                    sale_order so
                                    left join
                                    sale_order_line sol on so.id = sol.order_id
                                    left join
                                    kderp_internal_currency kic on sol.currency_id = kic.id
                                    inner join
                                    kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id 
                                    where 
                                    so.state not in ('draft','cancel') and sol.currency_id = kccl.currency_id
                                    union
                                    Select 
                                    so.contract_id,
                                    get_default_currency() as internal_curr_id,
                                    sum(coalesce(fncompute(kitc.id,get_default_currency(),dateofsubmit,'international'),0)*coalesce(sol.amount,0)) as amount
                                    from 
                                    sale_order so
                                    left join
                                    sale_order_line sol on so.id = sol.order_id
                                    left join
                                    kderp_internal_currency kic on sol.currency_id = kic.id
                                    left join
                                    kderp_international_currency kitc on kic.code = kitc.code
                                    where 
                                    so.state not in ('draft','cancel') and 
                                    sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
                                    group by
                                    internal_curr_id,so.contract_id,sol.job_type
                                    ) vwquotation_currs
                                left join
                                    kderp_internal_currency kic on internal_curr_id = kic.id
                                group by
                                    contract_id,internal_curr_id) vwtotal_contract_amount on kcc.id = contract_id
                            left join
                                kderp_internal_currency kic on currency_id = kic.id
                            left join
                                kderp_international_currency kitc on kic.code = kitc.code
                            left join
                                (Select -- Total Claim
                                    contract_id,
                                    currency_id,
                                    sum(kpfc.progress_amount + kpfc.adv_payment + kpfc.others  + kpfc.retention) as claim_amount
                                from
                                    kdvn_payment_from_client kpfc
                                where
                                    state not in ('draft','cancel')
                                group by
                                    currency_id,contract_id) vwtotal_claim on vwtotal_contract_amount.contract_id = vwtotal_claim.contract_id and kitc.id = vwtotal_claim.currency_id
                            left join
                                (Select -- Total Claim
                                contract_id,
                                sum(coalesce(fncompute(currency_id,get_default_currency(),date,'international'),0)*(kpfc.progress_amount + kpfc.adv_payment + kpfc.others  + kpfc.retention)) as claimed_amount
                                from
                                kdvn_payment_from_client kpfc
                                where
                                state not in ('draft','cancel')
                                group by
                                contract_id) vwclaimed on kcc.id = vwclaimed.contract_id 
                            group by
                            kcc.id,claimed_amount) vwcontracted on so.contract_id = vwcontracted.id
                        where kp.id in (%s)
                        group by
                            kp.id """ % (prj_id_list))
        for prj_list in cr.dictfetchall():
            tmp_id=prj_list.pop('id')
            res[tmp_id].update(prj_list)
        return res
    
    #Contracted Amount
    def _get_project_contracted_amount(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
           # project_info_cost_claim_lists={'prj_contracted_amount': 0.0,'prj_claimed_amount': 0.0}
            for id in ids:
                res[id]=0
            prj_id_list = ",".join(map(str, ids))
            
            cr.execute("""Select
                            kp.id,
                            sum(contracted_amount*(case when project_type='E' then coalesce(per_job_e,0) else coalesce(per_job_m,0) end)) as prj_contracted_amount
                        from
                            kdvn_project kp
                        left join
                            sale_order so on kp.id = project_e_id or kp.id = project_m_id
                        left join
                            (Select
                                quo_id,
                                case when 
                                    sum(coalesce(amount,0))=0 then 0 
                                else 
                                    sum(case when combine='E' then coalesce(amount,0) else 0 end)/sum(coalesce(amount,0)) 
                                end as per_job_e,
                                case when 
                                    sum(coalesce(amount,0))=0 then 0 
                                else 
                                    sum(case when combine='M' then coalesce(amount,0) else 0 end)/sum(coalesce(amount,0)) 
                                end as per_job_m
                            from
                                (Select
                                    so.id as quo_id,
                                    project_e_id as prj_id,
                                    sum(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'internal')*(sol.amount+sol.discount)) as amount,
                                    'E' as combine
                                from
                                    sale_order so
                                left join
                                    sale_order_line sol on so.id = sol.order_id and job_type='E'
                                where 
                                    coalesce(project_e_id,0)>0 and coalesce(contract_id,0)>0
                                group by
                                    contract_id,project_e_id,so.id
                                Union
                                Select
                                    so.id as quo_id,
                                    project_m_id as prj_id,
                                    sum(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'internal')*(sol.amount+sol.discount)) as amount,
                                    'M' as combine
                                from
                                    sale_order so
                                left join
                                    sale_order_line sol on so.id = sol.order_id and job_type='M'
                                where 
                                    coalesce(project_m_id,0)>0 and coalesce(contract_id,0)>0
                                group by
                                    contract_id,project_m_id,so.id) vwcombine_quos
                            group by
                                quo_id) vwjob_per on so.id = quo_id
                        left join
                            (Select
                                kcc.id,
                                coalesce(claimed_amount,0) as claimed_amount,
                                sum(coalesce(fncompute(kitc.id,get_default_currency(),registration_date,'international'),0)*(coalesce(camt,0)- coalesce(claim_amount,0))) + coalesce(claimed_amount,0) as contracted_amount
                            from
                                kdvn_contract_client kcc
                            left join
                                (Select
                                    contract_id,
                                    sum(amount) as camt,
                                    internal_curr_id as currency_id
                                from
                                    (Select 
                                    so.contract_id,
                                    sol.currency_id as internal_curr_id,
                                    coalesce(sol.amount,0)+coalesce(sol.discount,0) as amount
                                    from 
                                    sale_order so
                                    left join
                                    sale_order_line sol on so.id = sol.order_id
                                    left join
                                    kderp_internal_currency kic on sol.currency_id = kic.id
                                    inner join
                                    kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id 
                                    where 
                                    so.state not in ('draft','cancel') and sol.currency_id = kccl.currency_id
                                    union
                                    Select 
                                    so.contract_id,
                                    get_default_currency() as internal_curr_id,
                                    sum(coalesce(fncompute(kitc.id,get_default_currency(),dateofsubmit,'international'),0)*coalesce(sol.amount,0)) as amount
                                    from 
                                    sale_order so
                                    left join
                                    sale_order_line sol on so.id = sol.order_id
                                    left join
                                    kderp_internal_currency kic on sol.currency_id = kic.id
                                    left join
                                    kderp_international_currency kitc on kic.code = kitc.code
                                    where 
                                    so.state not in ('draft','cancel') and 
                                    sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
                                    group by
                                    internal_curr_id,so.contract_id,sol.job_type
                                    ) vwquotation_currs
                                left join
                                    kderp_internal_currency kic on internal_curr_id = kic.id
                                group by
                                    contract_id,internal_curr_id) vwtotal_contract_amount on kcc.id = contract_id
                            left join
                                kderp_internal_currency kic on currency_id = kic.id
                            left join
                                kderp_international_currency kitc on kic.code = kitc.code
                            left join
                                (Select -- Total Claim
                                    contract_id,
                                    currency_id,
                                    sum(kpfc.progress_amount + kpfc.adv_payment + kpfc.others  + kpfc.retention) as claim_amount
                                from
                                    kdvn_payment_from_client kpfc
                                where
                                    state not in ('draft','cancel')
                                group by
                                    currency_id,contract_id) vwtotal_claim on vwtotal_contract_amount.contract_id = vwtotal_claim.contract_id and kitc.id = vwtotal_claim.currency_id
                            left join
                                (Select -- Total Claim
                                contract_id,
                                sum(coalesce(fncompute(currency_id,get_default_currency(),date,'international'),0)*(kpfc.progress_amount + kpfc.adv_payment + kpfc.others  + kpfc.retention)) as claimed_amount
                                from
                                kdvn_payment_from_client kpfc
                                where
                                state not in ('draft','cancel')
                                group by
                                contract_id) vwclaimed on kcc.id = vwclaimed.contract_id 
                            group by
                            kcc.id,claimed_amount) vwcontracted on so.contract_id = vwcontracted.id
                        where kp.id in (%s)
                        group by
                            kp.id """ % (prj_id_list))
        for prj_id,contracted in cr.fetchall():
            res[prj_id] = contracted
        return res

    def _get_project_cost_claimed_amount(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            #project_info_cost_claim_lists={'prj_contracted_amount': 0.0,'prj_claimed_amount': 0.0}
            for id in ids:
                res[id]=0.0
            prj_id_list = ",".join(map(str, ids))
            
            cr.execute("""Select
                            kp.id,
                            sum(claimed_amount*(case when project_type='E' then coalesce(per_job_e,0) else coalesce(per_job_m,0) end)) as prj_claimed_amount
                        from
                            kdvn_project kp
                        left join
                            sale_order so on kp.id = project_e_id or kp.id = project_m_id
                        left join
                            (Select
                                quo_id,
                                case when 
                                    sum(coalesce(amount,0))=0 then 0 
                                else 
                                    sum(case when combine='E' then coalesce(amount,0) else 0 end)/sum(coalesce(amount,0)) 
                                end as per_job_e,
                                case when 
                                    sum(coalesce(amount,0))=0 then 0 
                                else 
                                    sum(case when combine='M' then coalesce(amount,0) else 0 end)/sum(coalesce(amount,0)) 
                                end as per_job_m
                            from
                                (Select
                                    so.id as quo_id,
                                    project_e_id as prj_id,
                                    sum(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'internal')*(sol.amount+sol.discount)) as amount,
                                    'E' as combine
                                from
                                    sale_order so
                                left join
                                    sale_order_line sol on so.id = sol.order_id and job_type='E'
                                where 
                                    coalesce(project_e_id,0)>0 and coalesce(contract_id,0)>0
                                group by
                                    contract_id,project_e_id,so.id
                                Union
                                Select
                                    so.id as quo_id,
                                    project_m_id as prj_id,
                                    sum(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'internal')*(sol.amount+sol.discount)) as amount,
                                    'M' as combine
                                from
                                    sale_order so
                                left join
                                    sale_order_line sol on so.id = sol.order_id and job_type='M'
                                where 
                                    coalesce(project_m_id,0)>0 and coalesce(contract_id,0)>0
                                group by
                                    contract_id,project_m_id,so.id) vwcombine_quos
                            group by
                                quo_id) vwjob_per on so.id = quo_id
                        left join
                            (Select
                                kcc.id,
                                coalesce(claimed_amount,0) as claimed_amount,
                                sum(coalesce(fncompute(kitc.id,get_default_currency(),registration_date,'international'),0)*(coalesce(camt,0)- coalesce(claim_amount,0))) + coalesce(claimed_amount,0) as contracted_amount
                            from
                                kdvn_contract_client kcc
                            left join
                                (Select
                                    contract_id,
                                    sum(amount) as camt,
                                    internal_curr_id as currency_id
                                from
                                    (Select 
                                    so.contract_id,
                                    sol.currency_id as internal_curr_id,
                                    coalesce(sol.amount,0)+coalesce(sol.discount,0) as amount
                                    from 
                                    sale_order so
                                    left join
                                    sale_order_line sol on so.id = sol.order_id
                                    left join
                                    kderp_internal_currency kic on sol.currency_id = kic.id
                                    inner join
                                    kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id 
                                    where 
                                    so.state not in ('draft','cancel') and sol.currency_id = kccl.currency_id
                                    union
                                    Select 
                                    so.contract_id,
                                    get_default_currency() as internal_curr_id,
                                    sum(coalesce(fncompute_international(kitc.id,get_default_currency(),dateofsubmit),0)*coalesce(sol.amount,0)) as amount
                                    from 
                                    sale_order so
                                    left join
                                    sale_order_line sol on so.id = sol.order_id
                                    left join
                                    kderp_internal_currency kic on sol.currency_id = kic.id
                                    left join
                                    kderp_international_currency kitc on kic.code = kitc.code
                                    where 
                                    so.state not in ('draft','cancel') and 
                                    sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
                                    group by
                                    internal_curr_id,so.contract_id,sol.job_type
                                    ) vwquotation_currs
                                left join
                                    kderp_internal_currency kic on internal_curr_id = kic.id
                                group by
                                    contract_id,internal_curr_id) vwtotal_contract_amount on kcc.id = contract_id
                            left join
                                kderp_internal_currency kic on currency_id = kic.id
                            left join
                                kderp_international_currency kitc on kic.code = kitc.code
                            left join
                                (Select -- Total Claim
                                    contract_id,
                                    currency_id,
                                    sum(kpfc.progress_amount + kpfc.adv_payment + kpfc.others  + kpfc.retention) as claim_amount
                                from
                                    kdvn_payment_from_client kpfc
                                where
                                    state not in ('draft','cancel')
                                group by
                                    currency_id,contract_id) vwtotal_claim on vwtotal_contract_amount.contract_id = vwtotal_claim.contract_id and kitc.id = vwtotal_claim.currency_id
                            left join
                                (Select -- Total Claim
                                contract_id,
                                sum(coalesce(fncompute(currency_id,get_default_currency(),date,'international'),0)*(kpfc.progress_amount + kpfc.adv_payment + kpfc.others  + kpfc.retention)) as claimed_amount
                                from
                                kdvn_payment_from_client kpfc
                                where
                                state not in ('draft','cancel')
                                group by
                                contract_id) vwclaimed on kcc.id = vwclaimed.contract_id 
                            group by
                            kcc.id,claimed_amount) vwcontracted on so.contract_id = vwcontracted.id
                        where kp.id in (%s)
                        group by
                            kp.id """ % (prj_id_list))
        for prj_id,claimed in cr.fetchall():
            res[prj_id] = claimed
        return res    

    def _get_check_over(self, cr, uid, ids, name, arg, context=None):
        res ={}
        for id in ids:
            res[id] = False 
        return res

    def _newcode_suggest(self,cr,uid,context={}):
        list_newcode = []
        cr.execute("Select \
                        * \
                    from\
                        vwview_new_project_code\
                    order by newcode")
        #list_newcode=''
        for ncode,nname in cr.fetchall(): 
            list_newcode.append((ncode,ncode +": " + nname))
            
        return list_newcode
    
    def _onchange_suggest_code(self, cr, uid, ids,new_code):
        if new_code:
            val={'value':{'code':new_code,'newcode_suggest':False}}
        else:
            val={}
        return val
    
    def _get_actual_completion_date(self, cr, uid, ids, name, args, context=None):
        res = {}
        for prj in self.browse(cr,uid,ids, context=context):
            tmp = None
            for quo in prj.quotation_lists:
                if quo.state=='done':
                    if not tmp or tmp<quo.contract_id.completion_date:
                        tmp = quo.contract_id.completion_date
            res[prj.id] = tmp
        return res
    
    def _get_quotations(self, cr, uid, ids, context=None):
        result = {}
        for so in self.pool.get('sale.order').browse(cr, uid, ids, context=context):
            result[so.project_e_id.id] = True
            result[so.project_m_id.id] = True
        return result.keys()

    def _get_contracts(self, cr, uid, ids, context=None):
        result = {}
        for ctc in self.pool.get('kdvn.contract.client').browse(cr, uid, ids, context=context):
            for quo in ctc.quotation_ids: 
                result[quo.project_e_id.id] = True
                result[quo.project_m_id.id] = True
        return result.keys()

    
    def _get_quotations_line(self, cr, uid, ids, context=None):
        result = {}
        for so in self.pool.get('sale.order.line').browse(cr, uid, ids, context=context):
            result[so.order_id.project_e_id.id] = True
            result[so.order_id.project_m_id.id] = True
            #result[so.order_id.project_m_id.id] = True
        return result.keys()
    
    def _get_internal_currency(self, cr, uid, ids, context=None):
        result = {}

        if len(ids)>1:
            ic_ids = ",".join(map(str,ids))
        else:
            ic_ids = ids[0]
        
        cr.execute("Select project_e_id as project_id from sale_order so left join sale_order_line sol on so.id = order_id where coalesce(project_e_id,0)>0  and sol.currency_id in (%s)\
                    union\
                    Select project_m_id as project_id from sale_order so left join sale_order_line sol on so.id = order_id where coalesce(project_m_id,0)>0  and sol.currency_id in (%s)" % (ic_ids,ic_ids))
        for id in cr.fetchall():
            try:
                id  = id [0]
            except:
                id = id
            result[id] = True
        
        return result.keys()
    
    def _get_internal_currency_rate(self, cr, uid, ids, context=None):
        result = {}

        if len(ids)>1:
            ic_ids = ",".join(map(str,ids))
        else:
            ic_ids = ids[0]
        
        cr.execute("Select project_e_id as project_id from sale_order so left join sale_order_line sol on so.id = order_id left join kderp_internal_currency_rate kicr on sol.currency_id = kicr.currency_id where coalesce(project_e_id,0)>0  and kicr.id in (%s)\
                    union\
                    Select project_m_id as project_id from sale_order so left join sale_order_line sol on so.id = order_id left join kderp_internal_currency_rate kicr on sol.currency_id = kicr.currency_id where coalesce(project_m_id,0)>0  and kicr.id in (%s)" % (ic_ids,ic_ids))
        for id in cr.fetchall():
            try:
                id  = id [0]
            except:
                id = id
            result[id] = True
        
        return result.keys()    
            
    
    _columns ={
                #size = 256 de nhap du job code khi loc du lieu
                'code':fields.char('Job Code',size=256,required=1,select=1),
                'newcode_suggest':fields.selection(_newcode_suggest,'New Code',size=16,store=False),
                'name':fields.char('Project Name',size=256,required=1,select=1),
                
                'registration_date':fields.date("Reg. Date",select=2),
                'started_date':fields.date('Started Date',select=2),
                'completion_date':fields.date('Completion Date',select=2),
                'closed_date':fields.date('Closed Date',select=2),
                
                'actual_completion_date':fields.function(_get_actual_completion_date,type='date',string='Actual Comp. Date',select=2,readonly=1,method=True,
                                                         store={
                                                                'sale.order': (_get_quotations, ['project_e_id','project_m_id','contract_id','state'], 20),
                                                                'kdvn.contract.client':(_get_contracts, ['completion_date'], 20)}),
                                
                'project_type':fields.selection([('E','Electrical'),('M','Mechanical')],"Project Type",select=1,required=True),
                'project_status':fields.selection([('doing','On-Going'),('done','Completed'),('closed','Closed'),('cancel','Canceled')],"Prj. Status"),
                
                'description':fields.text("Description"),
                
                'general_project_manager_id':fields.many2one("res.users",'G.P.M.'),
                'project_manager_id':fields.many2one("res.users",'P.M.'),
                'area_site_manager_id':fields.many2one("res.users",'A.S.M.'),
                'site_manager_id':fields.many2one('res.users','S.M.'),
                
                'budget_data_ids':fields.one2many('kdvn.budget.data','project_id','Budget Lines'),
                'budget_history_ids':fields.one2many('kdvn.budget.history','project_id','Budget History'),
                
                'prj_cost_summary_ids':fields.one2many("kderp.project.cost.summary","project_id","Cost Summary",readonly=True),
                'prj_contracted_summary_ids':fields.one2many("kderp.project.contracted.summary","project_id","Contracted Summary",readonly=True),
                'prj_estimated_summary_ids':fields.one2many("kderp.project.estimated.summary","project_id","Estimated Summary",readonly=True),
                'prj_budget_category_ids':fields.one2many('kderp.project.budget.category','project_id','Budget Categories',readonly=True),
                
                'contract_ids':fields.function(_get_contract_lists,relation='kdvn.contract.client',type='one2many',string='Contracts',method=True),
                'quotation_lists':fields.function(_get_quotation_lists,relation='sale.order',type='one2many',string='Quotations List',method=True),
                
                'owner_id':fields.many2one('res.partner','Owner'),
                'client_id':fields.many2one('res.partner','Client'),
                'address_id':fields.many2one('res.partner.address','Address'),
                'invoice_address_id':fields.many2one('res.partner.address','Invoice Address'),
                
                'project_currency_ids':fields.one2many('project.currency','project_id','Project Currencies'),
                'project_currency_reference':fields.function(_get_project_currency,
                                                    type='many2one',
                                                    relation="project.currency",
                                                    string="Cur.",
                                                    readonly=True,
                                                    method=True,
                                                    store=True,
                                                    help='Use below Currency System to update'),
                                                    
                'project_exrate_reference':fields.float('Ex.Rate',readonly=True),


               'prj_budget_total':fields.float('Job Budget',required=True),
                
               'check_over':fields.function(_get_check_over,string='Over',type='boolean',method=True) #Project is overred budget is True else False
                } 
    _sql_constraints = [('code_unique_project', 'unique (code)', 'KDVN Error: The code of the project must be unique !')] 
    _defaults = {  
        'project_type':lambda *x:"E",
        'project_status':lambda *x:"doing",
        'project_exrate_reference':lambda *x:0.0,
        'prj_budget_total':lambda *x:0.0,
        }
    
    def _onchange_client_id(self, cr, uid, ids, partner_id):
        invoice_addr_id = False
        address_id = False
        if partner_id:
            res = self.pool.get('res.partner').address_get(cr, uid, [partner_id], ['default', 'invoice'])
            invoice_addr_id = res['invoice']
            address_id = False
            if res['default']:
                address_id = res['default'] 
            if not invoice_addr_id and address_id:  
                invoice_addr_id =address_id
                
        result = {'value': {'invoice_address_id': invoice_addr_id,
                            'address_id':address_id}}
        return result    
kdvn_project()

class kdvn_contract_client(osv.osv):
    _name = "kdvn.contract.client"
    _description="KDVN Contract Client"
    _inherit='kdvn.contract.client'
    
    def _get_project_lists(self, cr, uid, ids, name, arg, context=None):
        res = {}
        #raise osv.except_osv("E",self._name)
        d1, d2 = self.pool.get('ir.rule').domain_get(cr, uid, 'kdvn.project')
        #raise osv.except_osv("E",d1)
        kp_ids = ",".join(map(str,d2))
        if len(kp_ids)>0:
            for id in ids:
                cr.execute('Select id from (SELECT project_e_id as id FROM sale_order WHERE contract_id=%s and coalesce(project_e_id,0)>0\
                            union\
                            SELECT project_m_id as id FROM sale_order WHERE contract_id=%s and coalesce(project_m_id,0)>0) vwtemp where id in (%s)' % (id,id,kp_ids))
                res[id] = [x[0] for x in cr.fetchall() if x[0]]
        else:
            for id in ids:
                cr.execute('Select id from (SELECT project_e_id as id FROM sale_order WHERE contract_id=%s and coalesce(project_e_id,0)>0\
                            union\
                            SELECT project_m_id as id FROM sale_order WHERE contract_id=%s and  coalesce(project_m_id,0)>0) vwtemp' % (id,id))
                res[id] = [x[0] for x in cr.fetchall() if x[0]]
        return res
    
    _columns={
              #'project_ids':fields.many2many('kdvn.project','kdvn_project_contract_rel','contract_id','project_id','Contracts'),
              #'project_ids':fields.function(_get_project_lists,relation='kdvn.project',type='one2many',string='Projects',method=True),
              }
    def init1(self,cr):
        #tools.sql.drop_view_if_exists(cr,'kdvn_project_contract_rel')
        cr.execute("create or replace view kdvn_project_contract_rel as\
                        Select \
                            contract_id,\
                            project_e_id as project_id\
                        from \
                            sale_order\
                        where \
                            state<>'cancel' and contract_id>0 and project_e_id>0\
                        union\
                        Select \
                            contract_id,\
                            project_m_id as project_id\
                        from \
                            sale_order \
                        where \
                            state<>'cancel' and contract_id>0 and project_m_id>0")
kdvn_contract_client()

class kderp_project_contracted_summary(osv.osv):
    _name = "kderp.project.contracted.summary"
    _description="KDERP Project Contracted Summary"
    _auto = False
    _rec_name = "project_id"
    _columns ={
               'project_id':fields.many2one('kdvn.project','Project'),
               'prj_contracted_amount':fields.float('Contracted Amt.'),
               'prj_claimed_amount':fields.float('Claimed Amt.'),
               'prj_received_amount':fields.float('Received Amt.')               
               }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_project_contracted_summary as
                    Select 
                        kp.id,
                        kp.id as project_id,
                        sum(coalesce(contracted_amount,0)) as prj_contracted_amount,
                        sum(coalesce(claimed_amount,0)) as prj_claimed_amount,
                        sum(coalesce(received_amount,0)) as prj_received_amount
                    from
                        kdvn_project kp
                    left join
                        kderp_contract_summary_line kcsl on kp.id = kcsl.project_id
                    group by
                        kp.id""")
kderp_project_contracted_summary()

class kderp_project_estimated_summary(osv.osv):
    _name = "kderp.project.estimated.summary"
    _description="KDERP Project Estimated Summary"
    _auto = False
    _rec_name = "project_id"
    _columns ={
               'project_id':fields.many2one('kdvn.project','Project'),
               'prj_estimated_internal_amount':fields.float('Internal Amt.'),
               'prj_estimated_endofterm_amount':fields.float('End Of Term Amt.'),
               
               'prj_exchange_rate_ids':fields.one2many('kderp.vwproject.exchange.rate.breakdown','project_id','Rates',readonly=True),
               
               'prj_breakdown_quotation_ids':fields.one2many('kderp.estimated.breakdown.quotation.not.yet_contract','project_id','Quotation Not Yet Contracted',readonly=True),
               'prj_breakdown_kpfc_ids':fields.one2many('kderp.project.claimed.breakdown','project_id','Claimed',readonly=True),
               'prj_breakdown_contracted_notyet_claimed_ids':fields.one2many('kderp.project.contracted.notyet.claimed.breakdown','project_id','Contracted Not Yet Claim',readonly=True),
               }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_project_estimated_summary as
                        Select
                            id,
                            id as project_id,
                            sum(coalesce(remain_amount,0)*fncompute(currency_id,get_default_currency(),registration_date,'internal'))+coalesce(claimed_amount,0)+coalesce(estimated_internal,0) as prj_estimated_internal_amount,--+estimated_internal
                            sum(coalesce(remain_amount,0)*fncompute_endofterm(currency_id,get_default_currency()))+coalesce(claimed_amount,0)+coalesce(estimated_endofterm,0) as prj_estimated_endofterm_amount
                        from
                            (Select 
                                kp.id,kp.registration_date,
                                coalesce(estimate_amount_internal_company_curr,0) as estimated_internal,
                                sum(vjfc.amount) - coalesce(claimed_subtotal,0) as remain_amount,
                                coalesce(claimed_amount_company_curr,0) as claimed_amount,
                                coalesce(estimate_amount_endofterm_company_curr,0) as estimated_endofterm,
                                vjfc.currency_id
                            from
                                kdvn_project kp 
                            left join
                                (Select
                                    project_id,
                                    sum(sub_total_endofterm) as estimate_amount_endofterm_company_curr,
                                    sum(sub_total_internal) as estimate_amount_internal_company_curr
                                from
                                    (Select 
                                        kp.id as project_id,
                                        sum(coalesce(fncompute_endofterm(sol_e.currency_id,get_default_currency()),0)*case when coalesce(expected_amount,0)>0 and coalesce(so_e.contract_id,0)=0 then expected_amount else coalesce(sol_e.discount,0)+coalesce(sol_e.amount,0) end) as sub_total_endofterm,
                                        sum(coalesce(fncompute(sol_e.currency_id,get_default_currency(),kp.registration_date,'internal'),0)*case when coalesce(expected_amount,0)>0 and coalesce(so_e.contract_id,0)=0 then expected_amount else coalesce(sol_e.discount,0)+coalesce(sol_e.amount,0) end) as sub_total_internal
                                    from
                                        kdvn_project kp
                                    left join
                                        sale_order so_e on kp.id = so_e.project_e_id
                                    left join
                                        sale_order_line sol_e on so_e.id = order_id and sol_e.job_type='E'        
                                    where 
                                        coalesce(project_e_id,0)>0 and so_e.state not in ('cancel','draft') and coalesce(so_e.contract_id,0)=0
                                    group by 
                                        kp.id
                                    Union
                                    Select 
                                        kp.id as project_id,
                                        sum(coalesce(fncompute_endofterm(sol_m.currency_id,get_default_currency()),0)*case when coalesce(expected_amount,0)>0 and coalesce(so_m.contract_id,0)=0 then expected_amount else coalesce(sol_m.discount,0)+coalesce(sol_m.amount,0) end)as sub_total_endofterm,
                                        sum(coalesce(fncompute(sol_m.currency_id,get_default_currency(),kp.registration_date,'internal'),0)*case when coalesce(expected_amount,0)>0 and coalesce(so_m.contract_id,0)=0 then expected_amount else coalesce(sol_m.discount,0)+coalesce(sol_m.amount,0) end) as sub_total_internal
                                    from
                                        kdvn_project kp
                                    left join
                                        sale_order so_m on kp.id = so_m.project_m_id
                                    left join
                                        sale_order_line sol_m on so_m.id = order_id and sol_m.job_type='M'
                                    where 
                                        coalesce(project_m_id,0)>0 and so_m.state not in ('cancel','draft') and coalesce(so_m.contract_id,0)=0
                                    group by 
                                        kp.id) vwtemp_project_in_default
                                Group By
                                    project_id) vwproject_before_contract on kp.id = vwproject_before_contract.project_id
                            left join
                                vwquotation_job_for_contract vjfc on kp.id = vjfc.project_id
                            left join --Claimed Amount follow currency
                                (Select 
                                    contract_id,
                                    project_id,
                                    kpfc.currency_id,
                                    sum(kpfcd.amount) as claimed_subtotal
                                from 
                                    kdvn_payment_from_client kpfc
                                left join
                                    kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                where
                                    state not in ('draft','cancel')
                                group by
                                    project_id,
                                    kpfc.currency_id,contract_id) vwclaimed on kp.id=vwclaimed.project_id and vjfc.currency_id = vwclaimed.currency_id and vjfc.contract_id = vwclaimed.contract_id
                            left join --Claimed Amount in Company Currency
                                (Select 
                                    project_id,
                                    sum(fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office')*kpfcd.amount) as claimed_amount_company_curr
                                from
                                    kdvn_payment_from_client kpfc
                                left join
                                    kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                where
                                    state not in ('draft','cancel')
                                group by
                                    project_id) vwclaimed_calculated on kp.id = vwclaimed_calculated.project_id
                            group by
                                claimed_amount_company_curr,
                                estimate_amount_internal_company_curr,
                                estimate_amount_endofterm_company_curr,
                                kp.id,
                                claimed_subtotal,vjfc.currency_id) vwtotal
                        group by 
                            id,claimed_amount,estimated_internal,estimated_endofterm""")
kderp_project_estimated_summary()

class kderp_project_cost_summary(osv.osv):
    _name = "kderp.project.cost.summary"
    _description="KDERP Project Cost Summary"
    _auto = False
    _rec_name = "project_id"
    _columns ={
               'project_id':fields.many2one('kdvn.project','Project'),
               'expense_amount':fields.float('Cost Amt.'),
               'paid_amount':fields.float('Paid Amt.')               
               }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_project_cost_summary as
                        Select 
                            kp.id as id,
                            kp.id as project_id,
                            coalesce(sum(expense_amount),0) as expense_amount,
                            coalesce(sum(paid_amount),0) as paid_amount
                        from
                            kdvn_project kp
                        left join
                            vwexpense_summary on kp.id = project_id 
                        Group by
                             kp.id""")
kderp_project_cost_summary()

class kderp_project_budget_category(osv.osv):
    _name = "kderp.project.budget.category"
    _description="KDERP Project Budget Category"
    _auto = False
    _rec_name = "project_id"
    _columns={
            'rent_dept':fields.float('RENT. DEPT.'),
            'kc_commission':fields.float('KC. COMMISSION'),
            'sv_fee':fields.float('SV. FEE'),
            'adm_drwng':fields.float('ADMIN. DRWAING'),
            'const_serv':fields.float('CONTRUCTION SERVICE'),
            'project_id':fields.many2one('kdvn.project','Project')
              }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_project_budget_category as 
                    Select
                        kp.id,
                        kp.id as project_id,
                        sum(rent_dept) as rent_dept,
                        sum(kc_commission) as kc_commission,
                        sum(sv_fee) as sv_fee,
                        sum(adm_drwng) as adm_drwng,
                        sum(const_serv) as const_serv
                    from
                        kdvn_project kp,
                        (Select 
                            bc.auto_rate as rent_dept,
                            0 as kc_commission,
                            0 as sv_fee,
                            0 as adm_drwng,
                            0 as const_serv
                        from 
                            budget_category bc
                        left join
                            budget_category bcp on bc.parent_id = bcp.id
                        where
                            bcp.cat_code='indirect_cost' and bc.cat_code='rent&dept'
                        Union
                        Select 
                            0 as rent_dept,
                            bc.auto_rate as kc_commission,
                            0 as sv_fee,
                            0 as adm_drwng,
                            0 as const_serv
                        from 
                            budget_category bc
                        left join
                            budget_category bcp on bc.parent_id = bcp.id
                        where
                            bcp.cat_code='indirect_cost' and bc.cat_code='kc_commission'
                        Union
                        Select 
                            0 as rent_dept,
                            0 as kc_commission,
                            bc.auto_rate as sv_fee,
                            0 as adm_drwng,
                            0 as const_serv
                        from 
                            budget_category bc
                        left join
                            budget_category bcp on bc.parent_id = bcp.id
                        where
                            bcp.cat_code='indirect_cost' and bc.cat_code='sv_fee'
                        Union
                        Select 
                            0 as rent_dept,
                            0 as kc_commission,
                            0 as sv_fee,
                            bc.auto_rate as adm_drwng,
                            0 as const_serv
                        from 
                            budget_category bc
                        left join
                            budget_category bcp on bc.parent_id = bcp.id
                        where
                            bcp.cat_code='indirect_cost' and bc.cat_code='adm&drwng'    
                        Union
                        Select 
                            0 as rent_dept,
                            0 as kc_commission,
                            0 as sv_fee,
                            0 as adm_drwng,
                            bc.auto_rate as const_serv
                        from 
                            budget_category bc
                        left join
                            budget_category bcp on bc.parent_id = bcp.id
                        where
                            bcp.cat_code='indirect_cost' and bc.cat_code='const.serv') vwtemp
                    Group by 
                        kp.id""")
kderp_project_budget_category()


#Break Down Estimated Amount

class kderp_estimated_breakdown_quotation_not_yet_contract(osv.osv):
    _name = 'kderp.estimated.breakdown.quotation.not.yet_contract'
    _auto = False
    _description = 'KDERP Estimated Breakdown Quotation Not Yet Contract'
    _columns = {
                'project_id':fields.many2one('kdvn.project','Project'),
                'quotation_id':fields.many2one('sale.order','Quotation'),
                'sub_total':fields.float("Sub-Total"),
                'currency_id':fields.many2one('kderp.currency','Currency'),
                'to_company_curr_eot':fields.float("E.O.T. Rate"),
                'to_company_curr_internal':fields.float("Internal Rate"),
                'estimated_endofterm':fields.float('Sub-Total (E.O.T. Rate)'),
                'estimated_internal':fields.float('Sub-Total (Internal Rate)'),
                }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_estimated_breakdown_quotation_not_yet_contract as
                        Select
                            row_number() OVER (ORDER BY project_id,quotation_id) AS id,
                            *,
                            sub_total*to_company_curr_internal as estimated_internal,
                            sub_total*to_company_curr_eot as estimated_endofterm
                        from
                            (Select 
                                kp.id as project_id,
                                so_e.id as quotation_id,
                                sol_e.currency_id,
                                coalesce(fncompute_endofterm(sol_e.currency_id,get_default_currency()),0) as to_company_curr_eot,
                                coalesce(fncompute(sol_e.currency_id,get_default_currency(),kp.registration_date,'internal'),0) as to_company_curr_internal,
                                sum(case when coalesce(expected_amount,0)>0 and coalesce(so_e.contract_id,0)=0 then expected_amount else coalesce(sol_e.discount,0)+coalesce(sol_e.amount,0) end) as sub_total
                            from
                                kdvn_project kp
                            left join
                                sale_order so_e on kp.id = so_e.project_e_id
                            left join
                                sale_order_line sol_e on so_e.id = order_id and sol_e.job_type='E'        
                            where 
                                coalesce(project_e_id,0)>0 and so_e.state not in ('cancel','draft') and coalesce(so_e.contract_id,0)=0
                            group by 
                                kp.id,
                                so_e.id,
                                sol_e.currency_id
                            Union
                            Select 
                                kp.id as project_id,
                                so_m.id as quotation_id,
                                sol_m.currency_id,
                                coalesce(fncompute_endofterm(sol_m.currency_id,get_default_currency()),0) as to_company_curr_eot,
                                coalesce(fncompute(sol_m.currency_id,get_default_currency(),kp.registration_date,'internal'),0) as to_company_curr_internal,
                                sum(case when coalesce(expected_amount,0)>0 and coalesce(so_m.contract_id,0)=0 then expected_amount else coalesce(sol_m.discount,0)+coalesce(sol_m.amount,0) end) as sub_total
                            from
                                kdvn_project kp
                            left join
                                sale_order so_m on kp.id = so_m.project_m_id
                            left join
                                sale_order_line sol_m on so_m.id = order_id and sol_m.job_type='M'
                            where 
                                coalesce(project_m_id,0)>0 and so_m.state not in ('cancel','draft') and coalesce(so_m.contract_id,0)=0
                            group by 
                                kp.id,
                                so_m.id,
                                sol_m.currency_id) vwtemp;""")
kderp_estimated_breakdown_quotation_not_yet_contract()

class kderp_project_claimed_breakdown(osv.osv):
    _name = "kderp.project.claimed.breakdown"
    _description = "KDERP Project Claimed Breakdown"
    _auto = False
    _columns = {
                'project_id':fields.many2one('kdvn.project','Project'),
                'kpfc_id':fields.many2one('kdvn.payment.from.client','Claimed'),
                'currency_id':fields.many2one('kderp.currency','Cur.'),
                'claimed_rate':fields.float('Claimed Rate'),
                'claim_amount':fields.float('Claimed Amt.'),
                'claim_amount_in_idr':fields.float('Claimed Amt. (IDR)'),
                }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_project_claimed_breakdown as
                       Select 
                            row_number() OVER (ORDER BY PROJECT_ID,KPFC.ID) as id,
                            project_id,
                            kpfc.id AS kpfc_id,
                            kpfc.currency_id,
                            fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office') as claimed_rate,
                            sum(kpfcd.amount) as claim_amount,
                            sum(kpfcd.amount)*fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office') as claim_amount_in_idr
                        from
                            kdvn_payment_from_client kpfc
                        left join
                            kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                        where
                            state not in ('draft','cancel')
                        group by
                            kpfc.id,project_id;""")
kderp_project_claimed_breakdown()

class kderp_project_contracted_notyet_claimed_breakdown(osv.osv):
    _name = "kderp.project.contracted.notyet.claimed.breakdown"
    _description = "KDERP Project Contracted and Not Yet Claimed Breakdown"
    _auto = False
    _columns = {
                'project_id':fields.many2one('kdvn.project','Project'),
                'contract_id':fields.many2one('kdvn.contract.client','Contract'),
                'remain_amount':fields.float('Sub-Total'),
                'currency_id':fields.many2one('kderp.currency','Cur.',context={'link':0}),
                'estimated_amount_endofterm':fields.float('Est. Amount  (E.O.T. Rate)'),
                'estimated_amount_internal':fields.float('Est. Amount (Internal Rate)')                
                }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_project_contracted_notyet_claimed_breakdown as
                        Select
                            row_number() over (order by kp.id,vjfc.contract_id,vjfc.currency_id) as id,
                            kp.id as project_id,
                            vjfc.contract_id,
                            vjfc.currency_id,
                            sum(vjfc.amount)-coalesce(claimed_subtotal,0) as remain_amount,
                            coalesce(fncompute(vjfc.currency_id,get_default_currency(),kp.registration_date,'internal'),0)*(sum(vjfc.amount)-coalesce(claimed_subtotal,0)) as estimated_amount_internal,
                            coalesce(fncompute_endofterm(vjfc.currency_id,get_default_currency()),0)*(sum(vjfc.amount)-coalesce(claimed_subtotal,0)) as estimated_amount_endofterm
                        from
                            kdvn_project kp 
                        left join
                            vwquotation_job_for_contract vjfc on kp.id = vjfc.project_id
                        left join --Claimed Amount follow currency
                            (Select 
                                project_id,
                                kpfc.currency_id,
                                contract_id,
                                sum(kpfcd.amount) as claimed_subtotal
                            from 
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            where
                                state not in ('draft','cancel')
                            group by
                                project_id,
                                kpfc.currency_id,
                                contract_id) vwclaimed on vjfc.contract_id = vwclaimed.contract_id and kp.id=vwclaimed.project_id and vjfc.currency_id = vwclaimed.currency_id
                        group by 
                            kp.id,claimed_subtotal,
                            vjfc.contract_id,
                            vjfc.currency_id
                        order by kp.id""")
kderp_project_contracted_notyet_claimed_breakdown()

class kderp_vwproject_exchange_rate_breakdown(osv.osv):
    _name='kderp.vwproject.exchange.rate.breakdown'
    _description='KDERP Project Exchange Rate Breakdown'
    _auto = False
    _columns = {
                'project_id':fields.many2one('kdvn.project','Project'),
                'dummy_char1':fields.char(" ",size=64),
                'internal_rate':fields.char('Internal Rate',size=256),
                'endofterm_rate':fields.char('E.O.T. Rate',size=256),
                }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_vwproject_exchange_rate_breakdown as 
                        Select
                            row_number() over (order by project_id,currency_id) as id,
                            project_id,
                            internal_rate,
                            endofterm_rate,
                            dummy_char1
                        from
                            (Select 
                                distinct 
                                kp.id as project_id,
                                currency_id,
                                kc.code || ' @ ' || trim(to_char(coalesce(fncompute(currency_id,get_default_currency(),kp.registration_date,'internal'),0),'9,999,990.00')) as internal_rate,
                                kc.code || ' @ ' || trim(to_char(coalesce(fncompute_endofterm(currency_id,get_default_currency()),0),'9,999,990.00')) as endofterm_rate,
                                lpad('',64,'|') as dummy_char1
                            from 
                                kdvn_project kp
                            left join
                                vwquotation_job_for_contract vjfc on kp.id = project_id
                            left join
                                kderp_currency kc on vjfc.currency_id = kc.id
                            where
                                kc.code<>'IDR'
                            Union 
                            Select
                                project_id,
                                currency_id,
                                kc.code || ' @ ' || trim(to_char(coalesce(fncompute(currency_id,get_default_currency(),kp.registration_date,'internal'),0),'9,999,990.00')) as internal_rate,
                                kc.code || ' @ ' || trim(to_char(coalesce(fncompute_endofterm(currency_id,get_default_currency()),0),'9,999,990.00')) as endofterm_rate,
                                lpad('',64,'|') as dummy_char1    
                            from
                                (Select 
                                    project_e_id as project_id,
                                    sol.currency_id
                                from 
                                    sale_order_line sol
                                left join
                                    sale_order so on sol.order_id = so.id and job_type='E'
                                where
                                    coalesce(so.contract_id,0)=0 and coalesce(project_e_id,0)>0
                                union
                                Select 
                                    project_m_id as project_id,
                                    sol.currency_id
                                from 
                                    sale_order_line sol
                                left join
                                    sale_order so on sol.order_id = so.id and job_type='M'
                                where
                                    coalesce(so.contract_id,0)=0 and coalesce(project_m_id,0)>0) vwcurrencies
                            left join
                                kdvn_project kp on project_id = kp.id
                            left join
                                kderp_currency kc on currency_id = kc.id
                            where 
                                kc.code<>'IDR') vwcurrencies""")
kderp_vwproject_exchange_rate_breakdown()